package edu.cs.usask.ca.silver.persistence.sql;

public interface SQLGroupStringsI {

	public static final String GROUP_INSERT = "INSERT INTO SGroup (g_identity, g_update_time, g_name, g_owner_identity, g_privacy, g_creationDate, g_description)"
			+ "VALUES ('%s', CURRENT_TIMESTAMP, '%s', '%s', '%s', CURRENT_TIMESTAMP, '%s')";

	public static final String GROUP_SELECT = "SELECT g_identity, g_update_time, g_name, g_creator, g_creationDate, g_description, g_nextMvIdPerProject "
			+ "FROM SGroup WHERE g_identity = '%s'";

	public static final String GROUP_SELECT_ALLIDS = "SELECT g_identity FROM SGroup";
	
	public static final String GROUP_SELECT_ALLNAMES = "SELECT g_name FROM SGroup";

	public static final String GROUP_SELECT_LAST_UPDATE_TIME = "SELECT g_update_time FROM SGroup";

	public static final String GROUP_SELECT_ANY = "SELECT * FROM SGroup";
	public static final String GROUP_SELECT_NAME = "SELECT g_name FROM SGroup WHERE g_identity = '%s'";
	public static final String GROUP_SELECT_OWNER_ID = "SELECT g_owner_identity FROM SGroup WHERE g_identity = '%s'";

	public static final String GROUP_SELECT_OWNER_NAME = "SELECT u_first_name FROM SUser WHERE u_identity = (SELECT g_owner_identity FROM SGroup WHERE g_identity = '%s')";

	public static final String GROUP_SELECT_PRIVACY = "SELECT g_privacy FROM SGroup WHERE g_identity = '%s'";
	public static final String GROUP_SELECT_CREATIONDATE = "SELECT g_creationDate FROM SGroup WHERE g_identity = '%s'";
	public static final String GROUP_SELECT_DESCRIPTION = "SELECT g_description FROM SGroup WHERE g_identity = '%s'";

	public static final String GROUP_SELECT_MPROJECT_IDS = "SELECT mp_identity FROM MProject WHERE mp_group_identity = '%s'";

	public static final String GROUP_SELECT_MPROJECT_ID_AND_NAME = "SELECT mp_identity, mp_name FROM MProject WHERE mp_group_identity = '%s'";

	public static final String GROUP_SELECT_ALL_USER_IDS_WITHIN_THIS_GROUP = "SELECT u2group_u_identity FROM U2Group WHERE u2group_g_identity = '%s'";

	public static final String GROUP_SELECT_ALL_GROUP_ID_AND_GROUP_NAME_AS_SET_OF_PAIRS = "SELECT g_identity, g_name FROM SGROUP";
	
	// DERBY does not support `LIMIT` -check
	// http://db.apache.org/derby/faq.html#limit
	// public static final String GROUP_SELECT_MOSTRECENTMVERSIONID =
	// "SELECT mv_ID FROM MVersion WHERE mv_g_identity = '%s' " +
	// "ORDER BY mv_creationDate DESC LIMIT 1";
	// public static final String GROUP_SELECT_MOSTRECENTMVERSIONID =
	// "SELECT mv_ID FROM (SELECT ROW_NUMBER() OVER() AS rownum, mv_ID FROM MVersion WHERE mv_g_identity = '%s' ORDER BY mv_creationDate DESC ) AS tmp MVersion WHERE rownum = 1";

	public static final String GROUP_SET_IDENTITY = "UPDATE SGroup SET g_identity = '%s', g_update_time = CURRENT_TIMESTAMP WHERE g_identity = '%s'";
	public static final String GROUP_SET_NAME = "UPDATE SGroup SET g_name = '%s' ,g_update_time = CURRENT_TIMESTAMP WHERE g_identity = '%s'";
	public static final String GROUP_SET_OWNER_ID = "UPDATE SGroup SET g_owner_identity = '%s' ,g_update_time = CURRENT_TIMESTAMP WHERE g_identity = '%s'";
	public static final String GROUP_SET_PRIVACY = "UPDATE SGroup SET g_privacy = '%s' ,g_update_time = CURRENT_TIMESTAMP WHERE g_identity = '%s'";
	public static final String GROUP_SET_DESCRIPTION = "UPDATE SGroup SET g_description = '%s' ,g_update_time = CURRENT_TIMESTAMP WHERE g_identity = '%s'";

	public static final String GROUP_DELETE = "DELETE FROM SGroup WHERE g_identity = '%s'";
	public static final String GROUP_REMOVE_USERS_FROM_THIS_GROUP = "DELETE FROM U2Group WHERE u2group_u_identity = '%s' AND u2group_g_identity = '%s'";

}
